/**
 * Package:com.goldcitynet.util.logs;
 * $Id: DBHelper.java,v 1.3 2009/06/23 07:19:50 zhangpei Exp $
 * Copyright(c) 2001-2005 www.afteryuan.com
 */
package com.afteryuan.util;

import org.apache.commons.logging.LogFactory;
import org.apache.commons.logging.Log;

import java.sql.*;
import java.util.List;
import java.util.StringTokenizer;

/**
 * 数据连接助手,单个连接其他数据库时使用
 * <p/>
 * <p><a href="asdasd.java.html"><i>View Source</i></a></p>
 *
 * @author <a href="mailto:afteryuan@afteryuan.com">$Author: zhangpei $</a>
 * @version $Revision: 1.3 $
 */
public class DBHelper {
    private static Log log = LogFactory.getLog(DBHelper.class);
    Statement stmt;
    boolean autoCommit;
    Connection connection;

    public    DBHelper(String className,String url,String user,String password) throws Exception {

        try {
            Class.forName(className).newInstance();
            connection = DriverManager.getConnection(url, user, password);
        }
        catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    public PreparedStatement getPreparedStmt(String sql) throws SQLException {
        PreparedStatement preStmt = null;
        try {
            preStmt = connection.prepareStatement(sql);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            throw ex;
        }
        return preStmt;
    }

    public void beginTrans() throws SQLException {
        try {
            autoCommit = connection.getAutoCommit();
            connection.setAutoCommit(false);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.print("beginTrans Errors");
            throw ex;
        }
    }

    public void commit() throws SQLException {
        try {
            connection.commit();
            connection.setAutoCommit(autoCommit);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.print("Commit Errors");
            throw ex;
        }
    }

    public void rollback() {
        try {
            connection.rollback();
            connection.setAutoCommit(autoCommit);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.print("Rollback Errors");
            //throw ex;
        }
    }

    public boolean getAutoCommit() throws SQLException {
        boolean result = false;
        try {
            result = connection.getAutoCommit();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("getAutoCommit fail" + ex.getMessage());
            throw ex;
        }
        return result;
    }

    //默认的情况下一次executeQuery(String sql)是一次事务。
    //但是可以调用beginTrans()，然后多次executeQuery(String sql)，最后commit()实现多sql的事务处理（注意在这种情况下如果发生违例，千万不要忘了在catch(){调用rollBack()})。
    //
    public ResultSet executeQuery(String sql) throws SQLException {
        ResultSet rs = null;
        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sql);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("conndb.executeQuery:" + ex.getMessage());
            throw ex;
        }
        return rs;
    }

    public void executeUpdate(String sql) throws SQLException {
        try {
            stmt = connection.createStatement();
            stmt.executeUpdate(sql);
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("conndb.executeUpdate:" + ex.getMessage());
            throw ex;
        }
    }

    //Method doBatch 的参数sql，是由一些sql语句拼起来的，用;隔开。可以将许多的sql放在一个事物中，一次执行。
    public int[] doBatch(String sql) throws SQLException {
        int[] rowResult = null;
        String a;
        try {
            //boolean autoCommit=cn.getAutoCommit();
            //cn.setAutoCommit(false);
            stmt = connection.createStatement();
            StringTokenizer st = new StringTokenizer(sql, ";");
            while (st.hasMoreTokens()) {
                a = st.nextToken();
                stmt.addBatch(a);
            }
            rowResult = stmt.executeBatch();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("conndb.doBatch:" + ex.getMessage());
            throw ex;
        }
        return rowResult;
    }

    public int[] doBatch(List<String> sqls) throws SQLException {
        int[] rowResult = null;
        try {
            //boolean autoCommit=cn.getAutoCommit();
            //cn.setAutoCommit(false);
            stmt = connection.createStatement();
            for (String sql : sqls) {
                stmt.addBatch(sql);
            }
            rowResult = stmt.executeBatch();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("conndb.doBatch:" + ex.getMessage());
            throw ex;
        }
        return rowResult;
    }

    public void close() throws SQLException {
        try {
            stmt.close();
            stmt = null;
            connection.close();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
            System.out.println("Closeing connection fail" + ex.getMessage());
            throw ex;

        }
    }

    public static void main(String[] args) throws Exception {
        String forname = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
        String url = "jdbc:sqlserver://219.153.65.42:1433";
        String username = "123";
        String password = "123";
        DBHelper db = new DBHelper(forname, url, username, password);
        String sql = "INSERT INTO [GServerInfo].[dbo].[GameUserInfo]           ([GUParentUserID]           ,[GUHighUserID0]           ,[GUHighUserID1]           ,[GUHighUserID2]           ,[GUHighUserID3]           ,[GUHighUserID4]           ,[GUAccount]           ,[GULevel]           ,[GUState]           ,[GUPasswd]           ,[GUName]           ,[GUFaceID]           ,[GUStateCongealFlag]           ,[GUUserRight]           ,[GUMasterRight]           ,[GUMeScore]           ,[GUMidScore]           ,[GULowScore]           ,[GUOccupancyRate]           ,[GUOccupancyRate_NoFlag]           ,[GULowOccupancyRate_Max]           ,[GULowOccupancyRate_Max_NoFlag]           ,[GULowOccupancyRate_Min]           ,[GULowOccupancyRate_Min_NoFlag]           ,[GURollbackRate]           ,[GUBetLimit]           ,[GURegisterTime]           ,[GULessUserCount]           ,[GUExtend_UserRight]           ,[GUExtend_TotalBetScore])     VALUES           (5           ,1           ,2           ,3           ,4           ,5           ,'afteryuann'           ,5           ,0           ,'96e79218965eb72c92a549dd5a330112'           ,'afteryuann'           ,0           ,0           ,0           ,0           ,10000           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0           ,0)";
        log.debug("--------------------------------");
        log.debug(sql);
        db.beginTrans();
        db.executeUpdate(sql);
        db.commit();
        db.close();
    }
}